""" 对国航数据 做清洗整理，并计算执行时间和计划时间的差值， 输出csv格式的数据 """
import os
import pandas as pd 
import re 


def convert_str_time(str_time, date):
    if pd.isnull(str_time): # 如果是空值什么都不做
        return None 

    if str_time[-1] == "+":
        date = date + pd.Timedelta(days = 1)
    if str_time[-1] == "-":
        date = date - pd.Timedelta(days = 1)
    
    real_date = pd.Timedelta(hours = int(str_time[0:2]), minutes = int(str_time[2:4]))

    real_date = date + real_date
    return real_date

def compute_time_delay(data, flight_date):
    # 数据清洗整理
    data.dropna(thresh = data.shape[1]- 4, inplace=True)  # 删除缺失数据

    data.columns = data.iloc[0] #重命名列表名称
    data = data.drop([3])  #整理数据
    data.index = data.loc[:,"序号"] # 改名字
    data["航班日期"] = pd.to_datetime(flight_date)
    data.drop(data[data["标注"] == "取消"].index, inplace=True) # s删除取消航班


    # 转为pd.timestampe
    for i in data.index:
        for k in ["预起","预起变更","离地","预达","预达变更","落地"]:
            data.loc[i,k] = convert_str_time(data.loc[i,k] , data.loc[i,"航班日期"])

    # 判断进出港
    for i in data.index:
        data.loc[i,"进出港"] = 1 if data.loc[i,"到站"] == "北京" else 0 # 进港为1出港为0
        if data.loc[i,"进出港"] == 1:
            try:
                
                data.loc[i,"误差"] = ((data.loc[i,"落地"] - data.loc[i,"预达"]).total_seconds() / 60)# 生成进港误差
            except:
                print("数据{}缺失，以删除该进港航班{}".format(data.loc[i,"落地"],i))
                data.drop([i])    
        if data.loc[i, "进出港"] == 0:
            try:
                data.loc[i,"误差"] = ((data.loc[i,"离地"] - data.loc[i,"预起"]).total_seconds() / 60)# 生成进港误差
            except:
                print("数据{}缺失，已删除该出港航班{}".format(data.loc[i,"落地"],i))
                data.drop([i])   
                
    print("处理{}航班".format(data.loc[1,"航班日期"]))
    return data



if __name__ =="__main__":

    url = "data\\首都机场国航代理航班数据"
    os_list = os.listdir(url)
    # 处理日期
    
    # 对文件夹中的每一个excel表处理
    for date in os_list:
        flight_date = re.findall("\d+",date) 
        data = pd.read_excel(url +"\\"+ date)
        flight_date = "2021-"+ str(flight_date[0])[0:2] + "-"+ str(flight_date[0])[2:4] # 处理日期数据

        # 数据处理，计算航班执行时间差
        data = compute_time_delay(data, flight_date)

        data.to_csv("data\\clean\\" + str(flight_date) + ".csv")
    